/**
 * 
 */
package com.nari.interfaces.dao.springjdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;

import com.nari.coreframe.dao.springjdbc.AbstractDaoImpl;
import com.nari.coreframe.dao.springjdbc.extendcore.ResultSetMapHandler;
import com.nari.interfaces.model.IHeatMeterData24Gather;
import com.nari.interfaces.utils.queryvo.HeatMeterData24RealtimeStatis;
import com.nari.platform.date.CalendarUtils;
import com.nari.platform.model.PageCond;
import com.nari.platform.model.ResultWithPageCond;
import com.nari.platform.sqlfunc.mysql.DateFunctionUtils4MySQL;
import com.nari.platform.utils.StringUtils;
import com.nari.rescontroller.dao.IResourceAccessControlDao;

/**
 * 
 * @author Xiaopeng
 * @date 2015-1-14
 */
public class HeatMeterData24GatherJdbcDaoImpl extends AbstractDaoImpl
		implements IHeatMeterData24GatherJdbcDao {
	private Logger logger = Logger.getLogger(HeatMeterData24GatherJdbcDaoImpl.class);
	private static final String hmd_24gather_dateTimeFormat = "yyyy-MM-dd HH";
	private static final String hmd_24gather_mysqlDateTimeFormat = "%Y-%m-%d %H";
//	private static final String hmd_24gather_oracleDateTimeFormat = "";
	private IResourceAccessControlDao iResourceAccessControlDao;
	
	/**
	 * 根据热量表表号、抄表时间查询热量表唯一标识
	 * @param meterNo
	 * @param recordTime
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2015-1-14
	 */
	public String queryMeterDataUID(String meterNo, Date recordTime) throws Exception {
		//抄表时间格式化
		String recordTimeStr = CalendarUtils.parseDateToString(recordTime, hmd_24gather_dateTimeFormat);
		String querySQL = "select hmd24.ID from serv_heat_meter_24data hmd24 " +
				"where hmd24.t_meter_no='"+meterNo+"' " +
				"and "+DateFunctionUtils4MySQL.generic_dateSqlField2stringFunc("hmd24.t_record_time", hmd_24gather_mysqlDateTimeFormat)+"='"+recordTimeStr+"' " +
				"limit 0,1";
//		System.out.println("querySQL:" + querySQL);
		logger.debug("querySQL:" + querySQL);
		List<String> resultList = this.jdbcTemplate.queryForList(querySQL, String.class);
		if(resultList!=null && resultList.size()>0 && resultList.get(0)!=null) {
			return resultList.get(0);
		}
		return null;
	}
	
	/**
	 * 查询可用于数据抽取任务的热量表表号列表
	 * @param extractRecordTime 数据抽取任务的抄表日期
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2015-1-20
	 */
	public List<String> queryAvailableExtractMeterNo(Date extractRecordTime) throws Exception {
		//日期字符串格式化
		String extractRecordTimeStr = CalendarUtils.parseDateToString(extractRecordTime);
		String querySQL = "select hmd24.t_meter_no as meterNo " +
						  "from serv_heat_meter_24data hmd24 " +
						  "where "+DateFunctionUtils4MySQL.dateSqlField2StringFunc("hmd24.t_record_time")+"='"+extractRecordTimeStr+"' " +
						  "group by hmd24.t_meter_no";
//		System.out.println("querySQL:" + querySQL);
		logger.debug("querySQL:" + querySQL);
		return this.jdbcTemplate.queryForList(querySQL, String.class);
	}
	
	/**
	 * 根据热表表号、抄表日期从分时集抄数据中抽取一条热量表数据
	 * 抽取规则：取累计热量为最大值的一条数据
	 * @param meterNo
	 * @param recordTime
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2015-1-20
	 */
	public IHeatMeterData24Gather queryAvailableExtractMeterData(String meterNo, 
			Date recordTime) throws Exception {
		String recordTimeStr = CalendarUtils.parseDateToString(recordTime);
		String querySQL = "select outer_hmd24.ID as id," +
								 "outer_hmd24.data_file_id as dataFileID," +
								 "outer_hmd24.t_meter_no as meterNo," +
								 "outer_hmd24.t_sum_heat as sumHeat," +
								 "outer_hmd24.sum_heat_unit as sumHeatUnit," +
								 "outer_hmd24.settle_account_heat as settleAccountHeat," +
								 "outer_hmd24.settleAccount_heat_unit as settleAccountHeatUnit," +
								 "outer_hmd24.t_sum_flow as sumFlow," +
								 "outer_hmd24.t_sum_flow_unit as sumFlowUnit," +
								 "outer_hmd24.t_record_time as recordTime," +
								 "outer_hmd24.t_instant_power as instantPower," +
								 "outer_hmd24.t_heat_power as heatPower," +
								 "outer_hmd24.t_heat_power_unit as heatPowerUnit," +
								 "outer_hmd24.supply_water_temp as supplyTemperature," +
								 "outer_hmd24.back_water_temp as backTemperature," +
								 "outer_hmd24.t_temp_diff as temperatureDiff," +
								 "outer_hmd24.t_instant_flow as instantFlow," +
								 "outer_hmd24.t_instant_flow_unit as instantFlowUnit," +
								 "outer_hmd24.t_run_hours as runHours," +
								 "outer_hmd24.t_wrong_hours as wrongHours," +
								 "outer_hmd24.t_sys_hours as sysHours," +
								 "outer_hmd24.t_wrong_info as wrongInfo," +
								 "outer_hmd24.t_data_type as dataType," +
								 "outer_hmd24.t_remark as remark " +
						  "from serv_heat_meter_24data outer_hmd24 " +
						  "where outer_hmd24.t_meter_no='"+meterNo+"' " +
						  "and "+DateFunctionUtils4MySQL.dateSqlField2StringFunc("outer_hmd24.t_record_time")+"='"+recordTimeStr+"' " +
						  "and outer_hmd24.t_sum_heat=(select max(hmd24.t_sum_heat) " +
						  							  "from serv_heat_meter_24data hmd24 " +
						  							  "where hmd24.t_meter_no='"+meterNo+"' " +
						  							  "and "+DateFunctionUtils4MySQL.dateSqlField2StringFunc("hmd24.t_record_time")+"='"+recordTimeStr+"') " +
						  "order by outer_hmd24.t_record_time desc " +
						  "limit 0,1";
//		System.out.println("querySQL:" + querySQL);
		logger.debug("querySQL:" + querySQL);
		return this.jdbcTemplate.queryForObject(querySQL, new RowMapper<IHeatMeterData24Gather>(){
			public IHeatMeterData24Gather mapRow(ResultSet rs, int index) throws SQLException {
				ResultSetMapHandler<IHeatMeterData24Gather> resultSetMapper = new ResultSetMapHandler<IHeatMeterData24Gather>();
				try {
					List<IHeatMeterData24Gather> meterDataList = resultSetMapper.mapResultSet2Object(rs, IHeatMeterData24Gather.class);
					if(meterDataList!=null && meterDataList.size()>0) {
						return meterDataList.get(0);
					}
				} catch (Exception e) {
					e.printStackTrace();
					throw new SQLException(e.getMessage());
				}
				return null;
			}
		});
	}
	
	/**
	 * 分页查询热量表分时数据的统计列表
	 * @param meterNo
	 * @param recordTimeStart
	 * @param recordTimeEnd
	 * @param pageCond
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2015-1-21
	 */
	public ResultWithPageCond<HeatMeterData24RealtimeStatis> queryMeterDataStatis(String meterNo, 
			Date recordTimeStart, Date recordTimeEnd, PageCond pageCond) throws Exception {
		//抄表日期格式化
		Date startTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTimeStart));
		String startTimeStr = CalendarUtils.parseDateTimeToString(startTime);
		Date endTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTimeEnd));
		String endTimeStr = CalendarUtils.parseDateTimeToString(endTime); 
		//无权限的分时热量表数据的统计查询
		String querySQL = "select hmd24.t_meter_no as meterNo," +
								  DateFunctionUtils4MySQL.dateSqlField2StringFunc("hmd24.t_record_time")+" as recordTime," +
								 "max(hmd24.t_sum_heat) as sumHeat," +
								 "max(hmd24.sum_heat_unit) as sumHeatUnit," +
								 "count(*) as uploadCounter " +
						  "from serv_heat_meter_24data hmd24 " +
						  "where hmd24.t_record_time between '"+startTimeStr+"' and '"+endTimeStr+"'";
		if(meterNo!=null && !"".equals(meterNo)) {
			querySQL += " and hmd24.t_meter_no='"+meterNo+"'";
		}
		querySQL += " group by hmd24.t_meter_no,"+DateFunctionUtils4MySQL.dateSqlField2StringFunc("hmd24.t_record_time");
		//权限资源匹配
		String authorityRes = iResourceAccessControlDao.queryAuthorityProjResSql();
		if(!"".equals(authorityRes)) { //非管理员用户授权项目权限
			List<Object> projectUIDList = this.jdbcTemplate.queryForList(authorityRes, Object.class);
			String formatProjectUIDList = StringUtils.formatIDsByList(projectUIDList);
			formatProjectUIDList = StringUtils.formatIDListWithSyntax(formatProjectUIDList, ",", "'");
			querySQL = "select inner_statis.meterNo as meterNo," +
							  "inner_statis.recordTime as recordTime," +
							  "inner_statis.sumHeat as sumHeat," +
							  "inner_statis.sumHeatUnit as sumHeatUnit," +
							  "inner_statis.uploadCounter as uploadCounter " +
					   "from base_heat_meter_info hm,("+querySQL+") inner_statis " +
					   "where hm.t_project_id in("+formatProjectUIDList+") " +
					   "and inner_statis.meterNo=hm.t_meter_no";
		}
		//结果集个数查询
		String couterSQL = "select count(*) from ("+querySQL+") statis_query";
//		System.out.println("couterSQL:"+couterSQL);
		logger.debug("couterSQL:"+couterSQL);
		int total = this.jdbcTemplate.queryForInt(couterSQL);
		//定制查询排序及分页
		querySQL = "select * from ("+querySQL+") statis_query " +
				"order by statis_query.recordTime desc,statis_query.meterNo asc " +
				"limit "+pageCond.getStart()+","+pageCond.getLimit();
//		System.out.println("querySQL:" + querySQL);
		logger.debug("querySQL:" + querySQL);
		//封装查询结果
		final List<HeatMeterData24RealtimeStatis> statisQueryList = new ArrayList<HeatMeterData24RealtimeStatis>();
		final ResultSetMapHandler<HeatMeterData24RealtimeStatis> resultSetMapper = new ResultSetMapHandler<HeatMeterData24RealtimeStatis>();
		this.getJdbcTemplate().query(querySQL, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				try {
					List<HeatMeterData24RealtimeStatis> meterDataList = resultSetMapper.mapResultSet2Object(rs, HeatMeterData24RealtimeStatis.class);
					//注意：这里已完成了ResultSet的迭代(取代了Spring JDBC的迭代处理)
					if(meterDataList!=null && meterDataList.size()>0) {
						statisQueryList.addAll(meterDataList);
					}
				} catch (Exception e) {
					e.printStackTrace();
					throw new SQLException(e.getMessage());
				}
			}
		});
		//构造返回类型
		pageCond.setTotal(total);
		ResultWithPageCond<HeatMeterData24RealtimeStatis> statisQueryPage = new ResultWithPageCond<HeatMeterData24RealtimeStatis>();
		statisQueryPage.setList(statisQueryList);
		statisQueryPage.setPageCond(pageCond);
		return statisQueryPage;
	}
	
	/**
	 * 根据热量表表号、抄表日期查询热量表分时数据的统计详细信息
	 * @param meterNo
	 * @param recordTime
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2015-1-22
	 */
	public HeatMeterData24RealtimeStatis queryMeterDataStatisDetail(String meterNo, 
			Date recordTime) throws Exception {
		//抄表日期格式化
		String recordTimeStr = CalendarUtils.parseDateToString(recordTime);
		String querySQL = "select hmd24.t_meter_no as meterNo," +
				  				  DateFunctionUtils4MySQL.dateSqlField2StringFunc("hmd24.t_record_time")+" as recordTime," +
				  				 "max(hmd24.t_sum_heat) as sumHeat," +
				  				 "max(hmd24.sum_heat_unit) as sumHeatUnit," +
				  				 "count(*) as uploadCounter " +
				  		  "from serv_heat_meter_24data hmd24 " +
				  		  "where "+DateFunctionUtils4MySQL.dateSqlField2StringFunc("hmd24.t_record_time")+"='"+recordTimeStr+"' " +
				  		  "and hmd24.t_meter_no='"+meterNo+"' " +
				  		  "group by hmd24.t_meter_no,"+DateFunctionUtils4MySQL.dateSqlField2StringFunc("hmd24.t_record_time");
//		System.out.println("querySQL:" + querySQL);
		logger.debug("querySQL:" + querySQL);
		return this.jdbcTemplate.queryForObject(querySQL, new RowMapper<HeatMeterData24RealtimeStatis>(){
			public HeatMeterData24RealtimeStatis mapRow(ResultSet rs, int index) throws SQLException {
				ResultSetMapHandler<HeatMeterData24RealtimeStatis> resultSetMapper = new ResultSetMapHandler<HeatMeterData24RealtimeStatis>();
				try {
					List<HeatMeterData24RealtimeStatis> meterDataList = resultSetMapper.mapResultSet2Object(rs, HeatMeterData24RealtimeStatis.class);
					if(meterDataList!=null && meterDataList.size()>0) {
						return meterDataList.get(0);
					}
				} catch (Exception e) {
					e.printStackTrace();
					throw new SQLException(e.getMessage());
				}
				return null;
			}
		});
	}

	/**
	 * @return the iResourceAccessControlDao
	 */
	public IResourceAccessControlDao getiResourceAccessControlDao() {
		return iResourceAccessControlDao;
	}

	/**
	 * @param iResourceAccessControlDao the iResourceAccessControlDao to set
	 */
	public void setiResourceAccessControlDao(
			IResourceAccessControlDao iResourceAccessControlDao) {
		this.iResourceAccessControlDao = iResourceAccessControlDao;
	}
	
}
